@page "/database/{ConnectionId:int}"
@using BlazorSodDbManager.Models
@using BlazorSodDbManager.Services
@using System.Data
@inject ConnectionManager ConnectionManager
@inject IDatabaseService DatabaseService
@inject NavigationManager NavigationManager
@inject IJSInteropService JSInterop

<div class="container-fluid">
    <div class="row">
        <div class="col-md-3">
            <div class="card">
                <div class="card-header">
                    <h5>@(_connection?.Name ?? "Database Explorer")</h5>
                </div>
                <div class="card-body p-0">
                    <div class="list-group list-group-flush">
                        <button class="list-group-item list-group-item-action @(_activeTab == "tables" ? "active" : "")" @onclick="@(() => ChangeTab("tables"))">
                            <i class="bi bi-table"></i> Tables
                        </button>
                        <button class="list-group-item list-group-item-action @(_activeTab == "views" ? "active" : "")" @onclick="@(() => ChangeTab("views"))">
                            <i class="bi bi-eye"></i> Views
                        </button>
                        <button class="list-group-item list-group-item-action @(_activeTab == "procedures" ? "active" : "")" @onclick="@(() => ChangeTab("procedures"))">
                            <i class="bi bi-code-square"></i> Stored Procedures
                        </button>
                        <button class="list-group-item list-group-item-action @(_activeTab == "functions" ? "active" : "")" @onclick="@(() => ChangeTab("functions"))">
                            <i class="bi bi-code"></i> Functions
                        </button>
                        <button class="list-group-item list-group-item-action @(_activeTab == "query" ? "active" : "")" @onclick="@(() => ChangeTab("query"))">
                            <i class="bi bi-file-earmark-code"></i> SQL Query
                        </button>
                    </div>
                </div>
            </div>

            @if (_tables.Count > 0 && _activeTab == "tables")
            {
                <div class="card mt-3">
                    <div class="card-header">
                        <h6>Tables</h6>
                        <div class="input-group input-group-sm">
                            <input type="text" class="form-control" placeholder="Filter tables..." @bind-value="_tableFilter" @bind-value:event="oninput" />
                        </div>
                    </div>
                    <div class="card-body p-0" style="max-height: 400px; overflow-y: auto;">
                        <div class="list-group list-group-flush">
                            @foreach (var table in _tables.Where(t => string.IsNullOrEmpty(_tableFilter) || t.TableName.Contains(_tableFilter, StringComparison.OrdinalIgnoreCase)))
                            {
                                <button class="list-group-item list-group-item-action @(_selectedTable?.TableName == table.TableName ? "active" : "")" @onclick="@(() => SelectTable(table))">
                                    <i class="bi bi-table"></i> @table.TableName
                                </button>
                            }
                        </div>
                    </div>
                </div>
            }

            @if (_views.Count > 0 && _activeTab == "views")
            {
                <div class="card mt-3">
                    <div class="card-header">
                        <h6>Views</h6>
                        <div class="input-group input-group-sm">
                            <input type="text" class="form-control" placeholder="Filter views..." @bind-value="_viewFilter" @bind-value:event="oninput" />
                        </div>
                    </div>
                    <div class="card-body p-0" style="max-height: 400px; overflow-y: auto;">
                        <div class="list-group list-group-flush">
                            @foreach (var view in _views.Where(v => string.IsNullOrEmpty(_viewFilter) || v.Contains(_viewFilter, StringComparison.OrdinalIgnoreCase)))
                            {
                                <button class="list-group-item list-group-item-action @(_selectedView == view ? "active" : "")" @onclick="@(() => SelectView(view))">
                                    <i class="bi bi-eye"></i> @view
                                </button>
                            }
                        </div>
                    </div>
                </div>
            }

            @if (_procedures.Count > 0 && _activeTab == "procedures")
            {
                <div class="card mt-3">
                    <div class="card-header">
                        <h6>Stored Procedures</h6>
                        <div class="input-group input-group-sm">
                            <input type="text" class="form-control" placeholder="Filter procedures..." @bind-value="_procedureFilter" @bind-value:event="oninput" />
                        </div>
                    </div>
                    <div class="card-body p-0" style="max-height: 400px; overflow-y: auto;">
                        <div class="list-group list-group-flush">
                            @foreach (var procedure in _procedures.Where(p => string.IsNullOrEmpty(_procedureFilter) || p.Contains(_procedureFilter, StringComparison.OrdinalIgnoreCase)))
                            {
                                <button class="list-group-item list-group-item-action @(_selectedProcedure == procedure ? "active" : "")" @onclick="@(() => SelectProcedure(procedure))">
                                    <i class="bi bi-code-square"></i> @procedure
                                </button>
                            }
                        </div>
                    </div>
                </div>
            }

            @if (_functions.Count > 0 && _activeTab == "functions")
            {
                <div class="card mt-3">
                    <div class="card-header">
                        <h6>Functions</h6>
                        <div class="input-group input-group-sm">
                            <input type="text" class="form-control" placeholder="Filter functions..." @bind-value="_functionFilter" @bind-value:event="oninput" />
                        </div>
                    </div>
                    <div class="card-body p-0" style="max-height: 400px; overflow-y: auto;">
                        <div class="list-group list-group-flush">
                            @foreach (var function in _functions.Where(f => string.IsNullOrEmpty(_functionFilter) || f.Contains(_functionFilter, StringComparison.OrdinalIgnoreCase)))
                            {
                                <button class="list-group-item list-group-item-action @(_selectedFunction == function ? "active" : "")" @onclick="@(() => SelectFunction(function))">
                                    <i class="bi bi-code"></i> @function
                                </button>
                            }
                        </div>
                    </div>
                </div>
            }
        </div>

        <div class="col-md-9">
            @if (_isLoading)
            {
                <div class="d-flex justify-content-center">
                    <div class="spinner-border" role="status">
                        <span class="visually-hidden">Loading...</span>
                    </div>
                </div>
            }
            else
            {
                @if (_activeTab == "tables" && _selectedTable != null && _viewingTableData==false && _generatedScript == null)
                {
                    <div class="card">
                        <div class="card-header d-flex justify-content-between align-items-center">
                            <h5>Table: @_selectedTable.TableName</h5>
                            <div>
                                <button class="btn btn-sm btn-outline-primary" @onclick="ViewTableData">View Data</button>
                                <div class="dropdown d-inline-block">
                                    <button class="btn btn-sm btn-outline-secondary dropdown-toggle" type="button" id="scriptDropdown" data-bs-toggle="dropdown" aria-expanded="false">
                                        Generate Script
                                    </button>
                                    <ul class="dropdown-menu" aria-labelledby="scriptDropdown">
                                        <li><button class="dropdown-item" @onclick="@(() => GenerateScript(ScriptType.Create))">Create Table</button></li>
                                        <li><button class="dropdown-item" @onclick="@(() => GenerateScript(ScriptType.Select))">Select</button></li>
                                        <li><button class="dropdown-item" @onclick="@(() => GenerateScript(ScriptType.Insert))">Insert</button></li>
                                        <li><button class="dropdown-item" @onclick="@(() => GenerateScript(ScriptType.Update))">Update</button></li>
                                        <li><button class="dropdown-item" @onclick="@(() => GenerateScript(ScriptType.Delete))">Delete</button></li>
                                    </ul>
                                </div>
                            </div>
                        </div>
                        <div class="card-body">
                            <h6>Columns</h6>
                            <div class="table-responsive">
                                <table class="table table-sm table-striped">
                                    <thead>
                                        <tr>
                                            <th>Name</th>
                                            <th>Data Type</th>
                                            <th>Nullable</th>
                                            <th>Primary Key</th>
                                            <th>Identity</th>
                                            <th>Default</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        @foreach (var column in _selectedTable.Columns.OrderBy(c => c.OrdinalPosition))
                                        {
                                            <tr>
                                                <td>@column.ColumnName</td>
                                                <td>
                                                    @column.DataType
                                                    @if (column.MaxLength > 0 && !column.DataType.Equals("text", StringComparison.OrdinalIgnoreCase))
                                                    {
                                                        <span>(@column.MaxLength)</span>
                                                    }
                                                </td>
                                                <td>@(column.IsNullable ? "Yes" : "No")</td>
                                                <td>@(column.IsPrimaryKey ? "Yes" : "No")</td>
                                                <td>@(column.IsIdentity ? "Yes" : "No")</td>
                                                <td>@column.DefaultValue</td>
                                            </tr>
                                        }
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                }
                else if (_activeTab == "query")
                {
                    <div class="card">
                        <div class="card-header">
                            <h5>SQL Query</h5>
                        </div>
                        <div class="card-body">
                            <div class="mb-3">
                                <textarea class="form-control" rows="10" @bind="_sqlQuery" placeholder="Enter your SQL query here..."></textarea>
                            </div>
                            <div class="mb-3">
                                <button class="btn btn-primary" @onclick="ExecuteQuery">Execute</button>
                            </div>
                            @if (_queryResult != null)
                            {
                                <div class="mt-3">
                                    <h6>Results</h6>
                                    @if (!_queryResult.IsSuccess)
                                    {
                                        <div class="alert alert-danger">
                                            @_queryResult.ErrorMessage
                                        </div>
                                    }
                                    else if (_queryResult.ResultSets.Count > 0)
                                    {
                                        <div class="table-responsive">
                                            <table class="table table-sm table-striped">
                                                <thead>
                                                    <tr>
                                                        @foreach (DataColumn column in _queryResult.ResultSets[0].Columns)
                                                        {
                                                            <th>@column.ColumnName</th>
                                                        }
                                                    </tr>
                                                </thead>
                                                <tbody>
                                                    @foreach (DataRow row in _queryResult.ResultSets[0].Rows)
                                                    {
                                                        <tr>
                                                            @foreach (var item in row.ItemArray)
                                                            {
                                                                <td>@(item == DBNull.Value ? "NULL" : item.ToString())</td>
                                                            }
                                                        </tr>
                                                    }
                                                </tbody>
                                            </table>
                                        </div>
                                        <div class="text-muted">
                                            Execution time: @_queryResult.ExecutionTime.TotalMilliseconds ms
                                        </div>
                                    }
                                    else
                                    {
                                        <div class="alert alert-success">
                                            Query executed successfully. Rows affected: @_queryResult.RowsAffected
                                        </div>
                                        <div class="text-muted">
                                            Execution time: @_queryResult.ExecutionTime.TotalMilliseconds ms
                                        </div>
                                    }
                                </div>
                            }
                        </div>
                    </div>
                }
                else if (_generatedScript != null)
                {
                    <div class="card">
                        <div class="card-header">
                            <h5>Generated Script</h5>
                        </div>
                        <div class="card-body">
                            <div class="mb-3">
                                <textarea class="form-control" rows="15" readonly>@_generatedScript</textarea>
                            </div>
                            <div class="mb-3">
                                <button class="btn btn-primary" @onclick="@(() => {_sqlQuery = _generatedScript; _generatedScript=null;_activeTab = "query";})">Use in Query Editor</button>
                                <button class="btn btn-secondary" @onclick="@(() => _generatedScript = null)">Close</button>
                            </div>
                        </div>
                    </div>
                }
                else if (_activeTab == "tables" && _viewingTableData)
                {
                    <div class="card">
                        <div class="card-header d-flex justify-content-between align-items-center">
                            <h5>Data: @_selectedTable.TableName</h5>
                            <div>
                                <button class="btn btn-sm btn-outline-secondary" @onclick="@(() => _viewingTableData = false)">Back to Structure</button>
                                <div class="btn-group ms-2">
                                    <button @onclick="@(() => ChangePageSize(10))" class="btn btn-sm btn-outline-primary @(_pageSize == 10 ? "active" : "")">10</button>
                                    <button @onclick="@(() => ChangePageSize(20))" class="btn btn-sm btn-outline-primary @(_pageSize == 20 ? "active" : "")">20</button>
                                    <button @onclick="@(() => ChangePageSize(50))" class="btn btn-sm btn-outline-primary @(_pageSize == 50 ? "active" : "")">50</button>
                                    <button @onclick="@(() => ChangePageSize(100))" class="btn btn-sm btn-outline-primary @(_pageSize == 100 ? "active" : "")">100</button>
                                </div>
                            </div>
                        </div>
                        <div class="card-body">
                            @if (_tableDataResult != null && _tableDataResult.IsSuccess && _tableDataResult.ResultSets.Count > 0)
                            {
                                <div class="table-responsive">
                                    <table class="table table-sm table-striped">
                                        <thead>
                                            <tr>
                                                @foreach (DataColumn column in _tableDataResult.ResultSets[0].Columns)
                                                {
                                                    <th>@column.ColumnName</th>
                                                }
                                            </tr>
                                        </thead>
                                        <tbody>
                                            @foreach (DataRow row in _tableDataResult.ResultSets[0].Rows)
                                            {
                                                <tr>
                                                    @foreach (var item in row.ItemArray)
                                                    {
                                                        <td>@(item == DBNull.Value ? "NULL" : item.ToString())</td>
                                                    }
                                                </tr>
                                            }
                                        </tbody>
                                    </table>
                                </div>

                                <!-- 分页部分 -->
                                <div class="d-flex justify-content-between align-items-center mt-3">
                                    <div>
                                        显示 @(_tableDataResult.ResultSets[0].Rows.Count) 条记录 @_tableDataResult.TotalRecords 条记录
                                    </div>
                                    <nav aria-label="Page navigation">
                                        <ul class="pagination">
                                            <li class="page-item @(!_tableDataResult.HasPreviousPage ? "disabled" : "")">
                                                <button class="page-link" @onclick="@(() => ChangePage(1))" disabled="@(!_tableDataResult.HasPreviousPage)">首页</button>
                                            </li>
                                            <li class="page-item @(!_tableDataResult.HasPreviousPage ? "disabled" : "")">
                                                <button class="page-link" @onclick="@(() => ChangePage(_currentPage - 1))" disabled="@(!_tableDataResult.HasPreviousPage)">上一页</button>
                                            </li>

                                            @{
                                                int startPage = Math.Max(1, _currentPage - 2);
                                                int endPage = Math.Min(_tableDataResult.TotalPages, startPage + 4);
                                                startPage = Math.Max(1, endPage - 4);

                                                for (int i = startPage; i <= endPage; i++)
                                                {
                                                    int pageNumber = i;
                                                    <li class="page-item @(pageNumber == _currentPage ? "active" : "")">
                                                        <button class="page-link" @onclick="@(() => ChangePage(pageNumber))">@pageNumber</button>
                                                    </li>
                                                }
                                            }

                                            <li class="page-item @(!_tableDataResult.HasNextPage ? "disabled" : "")">
                                                <button class="page-link" @onclick="@(() => ChangePage(_currentPage + 1))" disabled="@(!_tableDataResult.HasNextPage)">下一页</button>
                                            </li>
                                            <li class="page-item @(!_tableDataResult.HasNextPage ? "disabled" : "")">
                                                <button class="page-link" @onclick="@(() => ChangePage(_tableDataResult.TotalPages))" disabled="@(!_tableDataResult.HasNextPage)">末页</button>
                                            </li>
                                        </ul>
                                    </nav>
                                </div>

                                <div class="text-muted mt-2">
                                    查询时间: @_tableDataResult.ExecutionTime.TotalMilliseconds ms
                                </div>
                            }
                            else if (_tableDataResult != null && !_tableDataResult.IsSuccess)
                            {
                                <div class="alert alert-danger">
                                    @_tableDataResult.ErrorMessage
                                </div>
                            }
                        </div>
                    </div>
                }
            }
        </div>
    </div>
</div>

@code {
    [Parameter]
    public int ConnectionId { get; set; }

    private DatabaseConnection? _connection;
    private string _activeTab = "tables";
    private List<TableInfo> _tables = new List<TableInfo>();
    private List<string> _views = new List<string>();
    private List<string> _procedures = new List<string>();
    private List<string> _functions = new List<string>();
    private TableInfo? _selectedTable;
    private string? _selectedView;
    private string? _selectedProcedure;
    private string? _selectedFunction;
    private string _tableFilter = "";
    private string _viewFilter = "";
    private string _procedureFilter = "";
    private string _functionFilter = "";
    private bool _isLoading = false;
    private string _sqlQuery = "";
    private QueryResult? _queryResult;
    private string? _generatedScript;
    private bool _viewingTableData = false;
    private QueryResult? _tableDataResult;
    // 分页部分
    private int _currentPage = 1;
    private int _pageSize = 20;

    protected override async Task OnInitializedAsync()
    {
        _connection = ConnectionManager.GetConnection(ConnectionId);
        
        if (_connection == null)
        {
            NavigationManager.NavigateTo("/connections");
            return;
        }

        await LoadDatabaseObjects();
    }

    protected override async Task OnAfterRenderAsync(bool firstRender)
    {
        if (firstRender)
        {
            await JSInterop.InitializeBootstrapComponents();
        }
    }

    private async Task LoadDatabaseObjects()
    {
        _isLoading = true;
        StateHasChanged();

        try
        {
            _tables = await DatabaseService.GetTablesAsync(_connection);
            _views = await DatabaseService.GetViewsAsync(_connection);
            _procedures = await DatabaseService.GetStoredProceduresAsync(_connection);
            _functions = await DatabaseService.GetFunctionsAsync(_connection);
        }
        catch (Exception ex)
        {
            // Handle error
            Console.WriteLine(ex.Message);
        }
        finally
        {
            _isLoading = false;
            StateHasChanged();
        }
    }

    private void ChangeTab(string tab)
    {
        _activeTab = tab;
        _generatedScript = null;
        _viewingTableData = false;
    }

    private async Task SelectTable(TableInfo table)
    {
        _isLoading = true;
        _viewingTableData = false;
        StateHasChanged();

        try
        {
            _selectedTable = await DatabaseService.GetTableInfoAsync(_connection, table.TableName, table.Schema);
            _selectedView = null;
            _selectedProcedure = null;
            _selectedFunction = null;
        }
        catch (Exception ex)
        {
            // Handle error
            Console.WriteLine(ex.Message);
        }
        finally
        {
            _isLoading = false;
            StateHasChanged();
        }
    }

    private void SelectView(string view)
    {
        _selectedView = view;
        _selectedTable = null;
        _selectedProcedure = null;
        _selectedFunction = null;
        _viewingTableData = false;
    }

    private void SelectProcedure(string procedure)
    {
        _selectedProcedure = procedure;
        _selectedTable = null;
        _selectedView = null;
        _selectedFunction = null;
        _viewingTableData = false;
    }

    private void SelectFunction(string function)
    {
        _selectedFunction = function;
        _selectedTable = null;
        _selectedView = null;
        _selectedProcedure = null;
        _viewingTableData = false;
    }

    private async Task ExecuteQuery()
    {
        if (string.IsNullOrWhiteSpace(_sqlQuery))
            return;

        _isLoading = true;
        StateHasChanged();

        try
        {
            if (_sqlQuery.Trim().ToUpper().StartsWith("SELECT"))
            {
                _queryResult = await DatabaseService.ExecuteQueryAsync(_connection, _sqlQuery);
            }
            else
            {
                _queryResult = await DatabaseService.ExecuteNonQueryAsync(_connection, _sqlQuery);
            }
        }
        catch (Exception ex)
        {
            // Handle error
            Console.WriteLine(ex.Message);
        }
        finally
        {
            _isLoading = false;
            StateHasChanged();
        }
    }

    private enum ScriptType
    {
        Create,
        Select,
        Insert,
        Update,
        Delete
    }

    private async Task GenerateScript(ScriptType type)
    {
        if (_selectedTable == null)
            return;

        _isLoading = true;
        StateHasChanged();

        try
        {
            switch (type)
            {
                case ScriptType.Create:
                    _generatedScript = await DatabaseService.GenerateCreateTableScriptAsync(_connection, _selectedTable.TableName, _selectedTable.Schema);
                    break;
                case ScriptType.Select:
                    _generatedScript = await DatabaseService.GenerateSelectScriptAsync(_connection, _selectedTable.TableName, _selectedTable.Schema);
                    break;
                case ScriptType.Insert:
                    _generatedScript = await DatabaseService.GenerateInsertScriptAsync(_connection, _selectedTable.TableName, _selectedTable.Schema);
                    break;
                case ScriptType.Update:
                    _generatedScript = await DatabaseService.GenerateUpdateScriptAsync(_connection, _selectedTable.TableName, _selectedTable.Schema);
                    break;
                case ScriptType.Delete:
                    _generatedScript = await DatabaseService.GenerateDeleteScriptAsync(_connection, _selectedTable.TableName, _selectedTable.Schema);
                    break;
            }
        }
        catch (Exception ex)
        {
            // Handle error
            Console.WriteLine(ex.Message);
        }
        finally
        {
            _isLoading = false;
            StateHasChanged();
        }
    }


    private async Task ViewTableData()
    {
        if (_selectedTable == null)
            return;

        _isLoading = true;
        _viewingTableData = true;
        _currentPage = 1; // 重置为第一页
        StateHasChanged();

        try
        {
            await LoadTableDataPage();
        }
        catch (Exception ex)
        {
            // Handle error
            Console.WriteLine(ex.Message);
        }
        finally
        {
            _isLoading = false;
            StateHasChanged();
        }
    }
    private async Task ChangePage(int page)
    {
        if (_selectedTable == null || page < 1)
            return;

        _isLoading = true;
        _currentPage = page;
        StateHasChanged();

        try
        {
            await LoadTableDataPage();
        }
        catch (Exception ex)
        {
            // Handle error
            Console.WriteLine(ex.Message);
        }
        finally
        {
            _isLoading = false;
            StateHasChanged();
        }
    }

    private async Task ChangePageSize(int pageSize)
    {
        if (_selectedTable == null || pageSize < 1)
            return;

        _isLoading = true;
        _pageSize = pageSize;
        _currentPage = 1; // 重置第一页
        StateHasChanged();

        try
        {
            await LoadTableDataPage();
        }
        catch (Exception ex)
        {
            // Handle error
            Console.WriteLine(ex.Message);
        }
        finally
        {
            _isLoading = false;
            StateHasChanged();
        }
    }
    private async Task LoadTableDataPage()
    {
        if (_selectedTable == null)
            return;

        _tableDataResult = await DatabaseService.ExecuteQueryWithPagingAsync(
            _connection,
            _selectedTable.TableName,
            _selectedTable.Schema,
            _currentPage,
            _pageSize);
    }
} 